package com.java.zhuiyun.api.home.mapper;

import com.java.zhuiyun.api.home.entity.FrontDeskUser;
import com.java.zhuiyun.api.home.model.Model;
import com.java.zhuiyun.api.home.model.ModelOrder;
import com.java.zhuiyun.api.home.model.SalesVolumeYear;
import com.java.zhuiyun.api.member_manager_pc.entity.MemberType;
import com.java.zhuiyun.api.order.entity.Course;
import com.tencentcloudapi.cvm.v20170312.models.ReservedInstanceTypeItem;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @InterfaceName HomeMapper
 * @Description TODO
 * @Author wuwan
 * @Date 2023/8/14 20:06
 **/
@Mapper
public interface HomeMapper {
    /*
    * @Author wuwan
    * @Description 查询出都有什么地区
    * @Date 2023/8/31 10:24
    * @Return
    * @param null
    */
    @Select("select DISTINCT  region from front_desk_user")
    List<FrontDeskUser> statisticsOne();
    /*
    * @Author wuwan
    * @Description 查询出哪个地区的人数
    * @Date 2023/8/31 10:24
    * @Return
    * @param null
    */
    @Select("SELECT region AS name,COUNT(*) as value FROM front_desk_user WHERE region = #{region}")
    Model statisticsTwo(String region);

//    @Select("SELECT MONTH(create_time) AS month,COUNT(*) AS price FROM order" +
//            "WHERE order_status = 0 AND del_flag = 0 AND MONTH(create_time) = 13")
//    ModelOrder salesVolume();
//
    /*
    * @Author wuwan
    * @Description 月销量统计
    * @Date 2023/9/6 10:32
    * @Return com.java.zhuiyun.api.home.model.ModelOrder
    * @param month
    */
    @Select("SELECT MONTH(create_time) AS month,SUM(payment_price) AS price,COUNT(*) AS prices FROM orders WHERE order_status = 0 AND del_flag = 0 AND YEAR(create_time) = #{selectedYear} AND MONTH(create_time) = #{month} GROUP BY MONTH(create_time)")
    ModelOrder salesVolumes(String month,String selectedYear);
    /*
    * @Author wuwan
    * @Description 年销量统计
    * @Date 2023/9/6 11:04
    * @Return
    * @param null
    */
    @Select("SELECT YEAR(create_time) AS name,COUNT(*) AS value FROM orders WHERE order_status = 0 AND del_flag = 0 GROUP BY YEAR(create_time)")
    List<SalesVolumeYear> salesVolumesYear();

    @Select("select YEAR(create_time) AS name,SUM(payment_price) AS value from orders where order_status = 0 AND del_flag = 0 GROUP BY YEAR(create_time)")
    List<SalesVolumeYear> profitYear();

    /*
    * @Author wuwan
    * @Description 课程总盈利额
    * @Date 2023/9/6 17:06
    * @Return
    * @param null
    */
    @Select("select SUM(payment_price) AS price from orders where order_status = 0 AND del_flag = 0")
    SalesVolumeYear getgetqq();

    /*
     * @Author wuwan
     * @Description 课程总销量统计
     * @Date 2023/9/6 11:04
     * @Return
     * @param null
     */
    @Select("SELECT COUNT(*) AS value FROM orders WHERE order_status = 0 AND del_flag = 0")
    SalesVolumeYear salesVolumesYearss();
    /*
     * @Author wuwan
     * @Description 课程总盈利额
     * @Date 2023/9/6 17:06
     * @Return
     * @param null
     */
    @Select("select SUM(pay_price) AS prices from user_member_ship where payment = 1 AND del_flag = 0")
    SalesVolumeYear getmember();

    /*
     * @Author wuwan
     * @Description 会员总销量统计
     * @Date 2023/9/6 11:04
     * @Return
     * @param null
     */
    @Select("SELECT COUNT(*) AS valuess FROM user_member_ship WHERE payment = 1 AND del_flag = 0")
    SalesVolumeYear getgetmenber();

    /*
    * @Author wuwan
    * @Description 查询课程销量
    * @Date 2023/9/6 21:18
    * @Return
    * @param null
    */
    @Select("SELECT course_id AS name, COUNT(*) AS value, SUM(payment_price) AS price FROM orders WHERE del_flag = 0 GROUP BY course_id ORDER BY value DESC LIMIT 5;")
    List<SalesVolumeYear> courseRanking();

    /*
    * @Author wuwan
    * @Description 根据id查询课程名称
    * @Date 2023/9/6 21:20
    * @Return
    * @param null
    */
    @Select("select course_title,course_price,course_click from course where del_flag = 0 and id=#{id}")
    Course getCourseName(Integer id);

    @Select("select count(*),SUM(payment_price) AS price from orders where order_status = 0 AND del_flag = 0 AND YEAR(create_time) = #{selectedYear} ")
    SalesVolumeYear getAllqq(String selectedYear);

    @Select("select count(*),SUM(pay_price) AS prices from user_member_ship where payment = 1 AND del_flag = 0 AND YEAR(create_time) = #{selectedYear} ")
    SalesVolumeYear getAllmember(String selectedYear);
}
